package com.mayn.action;

import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.mayn.common.JfinalInit;

/***
 * 聚石塔RDS往API数据库增量同步工具
 * @author Administrator
 * 由于聚石塔RDS数据库中订单和宝贝数据仅保留最近6个月的数据，所以有必要将增量数据另外备份一份，供查询使用
 */
public class Sync_sys_info {
	
	private static final Logger LOG = LoggerFactory.getLogger(Sync_sys_info.class);

	public static void main(String[] args) {
		
		JfinalInit.InitDb(false,null);
		
		Sync();
		
		
	}
	
	public static void Sync(){
		long starTime = System.currentTimeMillis();
		LOG.info("本次同步开始");
		// 取数据库当前时间
		String startTimeString = Db.queryStr("select date_format(now(),'%Y-%m-%d %H:%i:%s')");
		LOG.info("取数据库当前时间:" + startTimeString);
		// 取数据库上次同步时间
		String sql = "SELECT date_format(sync_time,'%Y-%m-%d %H:%i:%s') from `sync_log` where log_status=1 order by log_id desc limit 1";
		String last_sync_datetime = Db.queryStr(sql);
		LOG.info("从日志表取上次同步时间:" + last_sync_datetime);
		try {
			if (last_sync_datetime == null || last_sync_datetime.trim().length() <= 0) {
				sql = "SELECT date_format(jdp_created,'%Y-%m-%d %H:%i:%s') from `jdp_tb_trade` order by jdp_created desc limit 1";
				last_sync_datetime = Db.queryStr(sql);
				LOG.info("从jdp_tb_trade取上次同步时间" + last_sync_datetime);
			}
			if (last_sync_datetime == null || last_sync_datetime.trim().length() <= 0) {
				LOG.error("请先执行初始化同步");
				return;
			}
			
			sync_item(last_sync_datetime);
			sync_trade(last_sync_datetime);
			save_sync_success(startTimeString);
		} catch (Exception e) {
			try {
				save_sync_error(startTimeString);
			} catch (Exception e1) {
				LOG.error(e1.getMessage());
			}
			LOG.error(e.getMessage());
		}
		long endTime = System.currentTimeMillis();
		long Time = endTime - starTime;
		Double sec = (double) (Time/1000);
		LOG.info("---本次任务耗时" + Time  + "豪秒/" + sec + "秒");
	}
	
	/**
	 * jdp_tb_item新增数据同步
	 * @param sync_datetime
	 * @throws Exception
	 */
	public static void sync_item(String sync_datetime) throws Exception {		
		String sql = "SELECT num_iid FROM jdp_tb_item jti where jti.jdp_created>='" + sync_datetime + "'";
		String countSql = "SELECT COUNT(*) FROM (" + sql + ") a";
		LOG.info("jdp_tb_item执行SQL:" + countSql);
		Long total_results = Db.use("jst").queryLong(countSql);
		LOG.info("jdp_tb_item新增数据,本次即将同步数据条数:" + total_results);
		if (total_results == 0) {
			LOG.info("jdp_tb_item无新增数据要同步");
		}
		List<Record> list_add = Db.use("jst").find(sql);
		sync_item_list(list_add);
		
		sql = "SELECT num_iid FROM jdp_tb_item jti where jti.jdp_modified>='" + sync_datetime + "'";
		countSql = "SELECT COUNT(*) FROM (" + sql + ") a";
		LOG.info("jdp_tb_item执行SQL:" + countSql);
		total_results = Db.use("jst").queryLong(countSql);
		LOG.info("jdp_tb_item更新数据,本次即将同步数据条数:" + total_results);
		if (total_results == 0) {
			LOG.info("jdp_tb_item无更新数据要同步");
		}
		List<Record> list_update = Db.use("jst").find(sql);
		sync_item_list(list_update);
		
	}
	
	/**
	 * jdp_tb_trade新增数据同步
	 * @param sync_datetime
	 * @throws Exception
	 */
	public static void sync_trade(String sync_datetime) throws Exception {		
		String sql = "SELECT tid FROM jdp_tb_trade jtt where jtt.jdp_created>='" + sync_datetime + "'";
		String countSql = "SELECT COUNT(*) FROM (" + sql + ") a";
		LOG.info("jdp_tb_trade执行SQL:" + countSql);
		Long total_results = Db.use("jst").queryLong(countSql);
		LOG.info("jdp_tb_trade新增数据,本次即将同步数据条数:" + total_results);
		if (total_results == 0) {
			LOG.info("jdp_tb_trade无新增数据要同步");
		}
		List<Record> list_add = Db.use("jst").find(sql);
		sync_trade_list(list_add);
		
		sql = "SELECT tid FROM jdp_tb_trade jtt where jtt.jdp_modified>='" + sync_datetime + "'";
		countSql = "SELECT COUNT(*) FROM (" + sql + ") a";
		LOG.info("jdp_tb_trade执行SQL:" + countSql);
		total_results = Db.use("jst").queryLong(countSql);
		LOG.info("jdp_tb_trade更新数据,本次即将同步数据条数:" + total_results);
		if (total_results == 0) {
			LOG.info("jdp_tb_trade无更新数据要同步");
		}
		List<Record> list_update = Db.use("jst").find(sql);
		sync_trade_list(list_update);
		
	}
	
	public static void sync_item_list(List<Record> list) throws Exception {		
		int syncCount = 0;
		int updateCount = 0;
		if (list.size() > 0) {
			syncCount = list.size();
			for (int i = 0; i < list.size(); i++) {
				Long num_iid = list.get(i).getLong("num_iid");
				String selectSql = "SELECT * FROM `jdp_tb_item` WHERE num_iid =" + num_iid ;
				Record selectResult = Db.use("jst").findFirst(selectSql);				
				String nick = selectResult.getStr("nick");
				String approve_status = selectResult.getStr("approve_status");
				String has_showcase = selectResult.getStr("has_showcase");
				Date created = selectResult.getDate("created");
				Date modified = selectResult.getDate("modified");
				String cid = selectResult.getStr("cid");
				String has_discount = selectResult.getStr("has_discount");
				String jdp_hashcode = selectResult.getStr("jdp_hashcode");
				String jdp_response = selectResult.getStr("jdp_response");
				int jdp_delete = selectResult.getInt("jdp_delete");
				Date jdp_created = selectResult.getDate("jdp_created");
				Date jdp_modified = selectResult.getDate("jdp_modified");
				
				Boolean c =save_jdp_tb_item(num_iid,nick,approve_status,has_showcase,created,modified,cid ,has_discount,jdp_hashcode,jdp_response,jdp_delete,jdp_created,jdp_modified);
				if(c){
					updateCount = updateCount + 1;
				}
			}
			LOG.info("jdp_tb_item共有" + syncCount + "条数据要同步" + ",成功同步:" + updateCount);
			if(syncCount >updateCount ){
				LOG.error("有错误");
			}
		}
	}
	
	
	public static void sync_trade_list(List<Record> list) throws Exception {		
		int syncCount = 0;
		int updateCount = 0;
		if (list.size() > 0) {
			syncCount = list.size();
			for (int i = 0; i < list.size(); i++) {
				Long tid = list.get(i).getLong("tid");
				String selectSql = "SELECT * FROM `jdp_tb_trade` WHERE tid =" + tid ;
				Record selectResult = Db.use("jst").findFirst(selectSql);				
				String status = selectResult.getStr("status");
				String type = selectResult.getStr("type");
				String seller_nick = selectResult.getStr("seller_nick");
				String buyer_nick = selectResult.getStr("buyer_nick");
				Date created = selectResult.getDate("created");
				Date modified = selectResult.getDate("modified");
				String jdp_hashcode = selectResult.getStr("jdp_hashcode");
				String jdp_response = selectResult.getStr("jdp_response");
				Date jdp_created = selectResult.getDate("jdp_created");
				Date jdp_modified = selectResult.getDate("jdp_modified");
				
				Boolean c =save_jdp_tb_trade(tid,status,type,seller_nick,buyer_nick,created,modified,jdp_hashcode,jdp_response,jdp_created,jdp_modified);
				if(c){
					updateCount = updateCount + 1;
				}
			}
			LOG.info("jdp_tb_trade共有" + syncCount + "条数据要同步" + ",成功同步:" + updateCount);
			if(syncCount >updateCount ){
				LOG.error("有错误");
			}
		}
	}
	
	public static Boolean save_jdp_tb_item(Long num_iid,String nick,String approve_status,String has_showcase,Date created,Date modified,String cid ,String has_discount,String jdp_hashcode,String jdp_response,int jdp_delete,Date jdp_created,Date jdp_modified){
		try{
			String updateSql = "UPDATE `jdp_tb_item` SET "
					+ " nick = '" + nick +  "'"
					+ ",approve_status = '" + approve_status +  "'"
					+ ",has_showcase = '" + has_showcase +  "'"
					+ ",created = '" + created +  "'"
					+ ",modified = '" + modified +  "'"
					+ ",cid = '" + cid +  "'"
					+ ",has_discount = '" + has_discount +  "'"
					+ ",jdp_hashcode = '" + jdp_hashcode +  "'"
					+ ",jdp_response = '" + jdp_response +  "'"
					+ ",jdp_delete = '" + jdp_delete +  "'"
					+ ",jdp_created = '" + jdp_created +  "'"
					+ ",jdp_modified = '" + jdp_modified +  "'"
					+ " WHERE num_iid=" + num_iid;
			String insertSql = "INSERT INTO `jdp_tb_item` (`num_iid`, `nick`, `approve_status`, `has_showcase`, `created`, `modified`, `cid`, `has_discount`, `jdp_hashcode`, `jdp_response`, `jdp_delete`, `jdp_created`, `jdp_modified`) VALUES ("
					+ num_iid 
					+ ",'" + nick +  "'"
					+ ",'" + approve_status +  "'"
					+ ",'" + has_showcase +  "'"
					+ ",'" + created +  "'"
					+ ",'" + modified +  "'"
					+ ",'" + cid +  "'"
					+ ",'" + has_discount +  "'"
					+ ",'" + jdp_hashcode +  "'"
					+ ",'" + jdp_response +  "'"
					+ ",'" + jdp_delete +  "'"
					+ ",'" + jdp_created +  "'"
					+ ",'" + jdp_modified +  "'"
					+ ")";
			String checkSql = "SELECT COUNT(*) FROM `jdp_tb_item` WHERE num_iid =" + num_iid;
			Long c = Db.queryLong(checkSql);
			// 判断数据存在，update or insert
			if (c > 0) {
				Db.update(updateSql);
				LOG.debug("update num_iid=" + num_iid);
			} else {
				// 数据不存在，插入
				Db.update(insertSql);
				LOG.debug("insert num_iid=" + num_iid);
			}
			return true;
		}
		catch(Exception e){
			LOG.error(e.getMessage());
			return false;
		}
	}
	
	
	public static Boolean save_jdp_tb_trade(Long tid,String status,String type,String seller_nick,String buyer_nick,Date created,Date modified,String jdp_hashcode,String jdp_response,Date jdp_created,Date jdp_modified){
		try{
			String updateSql = "UPDATE `jdp_tb_trade` SET "
					+ " status = '" + status +  "'"
					+ ",type = '" + type +  "'"
					+ ",seller_nick = '" + seller_nick +  "'"
					+ ",buyer_nick = '" + buyer_nick +  "'"
					+ ",created = '" + created +  "'"
					+ ",modified = '" + modified +  "'"
					+ ",jdp_hashcode = '" + jdp_hashcode +  "'"
					+ ",jdp_response = '" + jdp_response +  "'"
					+ ",jdp_created = '" + jdp_created +  "'"
					+ ",jdp_modified = '" + jdp_modified +  "'"
					+ " WHERE tid=" + tid;
			String insertSql = "INSERT INTO `jdp_tb_trade` (`tid`, `status`, `type`, `seller_nick`, `buyer_nick`, `created`, `modified`, `jdp_hashcode`, `jdp_response`, `jdp_created`, `jdp_modified`) VALUES ("
					+ tid 
					+ ",'" + status +  "'"
					+ ",'" + type +  "'"
					+ ",'" + seller_nick +  "'"
					+ ",'" + buyer_nick +  "'"
					+ ",'" + created +  "'"
					+ ",'" + modified +  "'"
					+ ",'" + jdp_hashcode +  "'"
					+ ",'" + jdp_response +  "'"
					+ ",'" + jdp_created +  "'"
					+ ",'" + jdp_modified +  "'"
					+ ")";
			String checkSql = "SELECT COUNT(*) FROM `jdp_tb_trade` WHERE tid =" + tid;
			Long c = Db.queryLong(checkSql);
			// 判断数据存在，update or insert
			if (c > 0) {
				Db.update(updateSql);
				LOG.debug("update tid=" + tid);
			} else {
				// 数据不存在，插入
				Db.update(insertSql);
				LOG.debug("insert tid=" + tid);
			}
			return true;
		}
		catch(Exception e){
			LOG.error(e.getMessage());
			return false;
		}
	}
	
	public static void save_sync_success(String sync_datetime) throws Exception {
		String sql = "INSERT INTO `sync_log` (sync_time,log_status,log_remark) value('"
				+ sync_datetime + "',1,'同步成功')";
		Db.update(sql);
		LOG.info("本次同步成功,记录本次同步开始时间:" + sync_datetime);
	}

	public static void save_sync_error(String sync_datetime) throws Exception {
		String sql = "INSERT INTO `sync_log` (sync_time,log_status,log_remark) value('"
				+ sync_datetime + "',0,'同步失败')";
		Db.update(sql);
		LOG.info("本次同步失败,记录本次同步开始时间:" + sync_datetime);
	}
	

}
